package com.clqteam.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.clqteam.dao.SubmitRejectOrderDao;
import com.clqteam.model.Product;
import com.clqteam.util.JDBCutil;

public class SubmitRejectOrderDaoImpl implements SubmitRejectOrderDao{

	@Override
	public double submitOrder(String code, String tbName, int vId, int oClassId, int eId, String oTime) {

			Connection con = JDBCutil.getConnection();
			//根据识别码(code)在order表中插入一条信息
			StringBuffer sql1 = new StringBuffer();
			sql1.append("INSERT INTO s_order(CODE) VALUE(?)");
			try {
				PreparedStatement pst = con.prepareStatement(sql1.toString());
				pst.setString(1, code);
				pst.executeUpdate();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//根据识别码取出自增长订单号(oId)
			int oId = 0;//订单号
			StringBuffer sql2 = new StringBuffer();
			sql2.append("SELECT s_order.oId FROM s_order");
			sql2.append(" WHERE s_order.code = ?");
			try {
				PreparedStatement pst = con.prepareStatement(sql2.toString());
				pst.setString(1, code);
				ResultSet rs = pst.executeQuery();
				if(rs.next()){
					oId = rs.getInt("oId");
				}			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//将该订单号加入收支表
			StringBuffer sql12 = new StringBuffer();
			sql12.append("INSERT INTO s_accountant(oId) VALUES(?)");
			try {
				PreparedStatement pst = con.prepareStatement(sql12.toString());
				pst.setInt(1, oId);
				pst.executeUpdate();			
			} catch (SQLException e2) {
				// TODO Auto-generated catch block
				e2.printStackTrace();
			}
			
			//根据员工号对应的临时表取出订单信息
			List<Product> pros = new ArrayList<Product>();
			StringBuffer sql3 = new StringBuffer();
			sql3.append("SELECT * FROM ");
			sql3.append(tbName);
			try {
				PreparedStatement pst = con.prepareStatement(sql3.toString());
				ResultSet rs = pst.executeQuery();
				while(rs.next()){
					Product pro = new Product();
					pro.setpId(rs.getInt("ptId"));
					pro.setPoAmount(rs.getInt("ptAmount"));
					pros.add(pro);
				}			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			//根据会员ID获取折扣率和积分，积分转换率
			double discount = 1;//折扣率
			int point = 0;//积分
			double rate = 0;
			if(vId!=0){
				StringBuffer sql4 = new StringBuffer();
				sql4.append("SELECT s_vip.vDiscount,s_vip.vPoint,s_vip.pitRate");
				sql4.append(" FROM s_vip");
				sql4.append(" WHERE vId = ?");
				try {
					PreparedStatement pst = con.prepareStatement(sql4.toString());
					pst.setInt(1, vId);
					ResultSet rs = pst.executeQuery();
					if(rs.next()){
						discount = rs.getDouble("vDiscount");
						point = rs.getInt("vPoint");
						rate = rs.getDouble("pitRate");
					}
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
			}		
			
			//将临时订单表中的信息写入订单详情表中	
			double oMoney = 0;//订单总价
			StringBuffer sql6 = new StringBuffer();
			sql6.append("INSERT INTO s_orderinfo(oId,pId,poAmount,poPrice) VALUE(?,?,?,?)");
			try {
				PreparedStatement pst = con.prepareStatement(sql6.toString());
				for(Product pro:pros){
					pst.setInt(1, oId);
					pst.setInt(2, pro.getpId());
					pst.setInt(3, pro.getPoAmount());
					//根据商品Id获取商品预售价
					StringBuffer sql7 = new StringBuffer();
					double price = 0;
					sql7.append("SELECT s_product.pPrice FROM s_product WHERE pId = ?");
					PreparedStatement pst0 = con.prepareStatement(sql7.toString());
					pst0.setInt(1, pro.getpId());
					ResultSet rs0 = pst0.executeQuery();
					if(rs0.next()){
						price = rs0.getDouble("pPrice");
					}
					double poPrice = price*discount;//单项总价
					pst.setDouble(4, poPrice);
					pst.executeUpdate();		
					oMoney += poPrice*pro.getPoAmount();
					//获取库存
					StringBuffer sql10 = new StringBuffer();
					int pAmount = 0;
					sql10.append("SELECT s_product.pAmount FROM s_product WHERE pId = ?");
					PreparedStatement pst2 = con.prepareStatement(sql10.toString());
					pst2.setInt(1, pro.getpId());
					ResultSet rs2 = pst2.executeQuery();
					if(rs2.next()){
						pAmount = rs2.getInt("pAmount");
					}
					//增加库存
					StringBuffer sql9 = new StringBuffer();
					sql9.append("UPDATE s_product SET s_product.pAmount = ? WHERE pId = ?");
					PreparedStatement pst1 = con.prepareStatement(sql9.toString());
					pst1.setInt(1, pAmount+pro.getPoAmount());
					pst1.setInt(2, pro.getpId());
					pst1.executeUpdate();
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			
			//减少会员积分
			if(vId!=0){
				StringBuffer sql11 = new StringBuffer();
				sql11.append("UPDATE s_vip SET s_vip.vPoint = ? WHERE s_vip.vId = ?");
				try {
					PreparedStatement pst = con.prepareStatement(sql11.toString());
					pst.setInt(1, point- (int)Math.round(oMoney*rate));
					pst.setInt(2, vId);
					pst.executeUpdate();
				} catch (SQLException e1) {
					// TODO Auto-generated catch block
					e1.printStackTrace();
				}
			}
			//根据订单详情表，填写订单表
			StringBuffer sql8 = new StringBuffer();
			sql8.append("UPDATE s_order SET oClassId=?,oMoney=?,eId=?,oTime=?,vId=?");
			sql8.append(" WHERE oId=?");
			try {
				PreparedStatement pst = con.prepareStatement(sql8.toString());
				pst.setInt(1, oClassId);
				pst.setDouble(2, oMoney);
				pst.setInt(3, eId);
				pst.setString(4, oTime);
				pst.setInt(5, vId);
				pst.setInt(6, oId);
				pst.executeUpdate();
				JDBCutil.closeJDBC(pst, con);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}		
			return oMoney;
		}

}
